"""
Case Type   : 故障&可靠性测试
Case Name   : 闰年跳转后，并发执行数据库创建以及vacuum，drop database
Create At   : 2021/01/08
Owner       : opengauss
Description :
    1..创建数据库test
    2.创建表空间
    3.设置操作系统时间为2020-02-28 23:58:00
    4.修改数据库
    5.创建数据库的同时执行vacuum
    6.修改数据库
    7.重复3-6 100次
Expect      :
    1.创建数据库成功
    2.创建表空间成功
    3.设置成功
    4.修改成功
    5.VACUUM成功
    6.修改成功
    7.成功率100%
History     :
    Modified by @wan005 2021/1/22: 增加ntp服务安装步骤
    Modified by @wan005 2021/1/27：vacuum提示warning正常，非问题，修改断言
    Modified by @wan005 2021/3/23: 增加环境时区修复步骤
    Modified by ningyali 2024/7/9 优化用例解决一致性问题
"""

import unittest

from yat.test import Node

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonSH import RestartDbCluster
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class DdlDatabase(unittest.TestCase):
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info('---Opengauss_Reliability_Dbsys_Case236.py 开始执行-----')
        self.root_nodes_tuple = ('PrimaryRoot', 'Standby1Root', 'Standby2Root')
        self.pri_sh = CommonSH(self.nodes_tuple[0])
        self.pri_user = self.pri_sh.node
        self.pri_root = Node(node=self.root_nodes_tuple[0])
        self.sta1_root = Node(node=self.root_nodes_tuple[1])
        self.sta1_user = Node(node=self.nodes_tuple[1])
        self.sta2_user = Node(node=self.nodes_tuple[2])
        self.constant = Constant()
        self.common = Common(self.root_nodes_tuple[0])
        self.dbname = 'test_db'
        self.dbname1 = 'test_db1'
        self.dbspace = 'dbspace'

        result = self.common.check_system_timezone(*self.root_nodes_tuple)
        self.log.info(result)
        self.assertTrue(result)

        self.log.info('=====安装ntp服务=======')
        result = self.pri_root.sh('yum install -y ntp').result()
        self.log.info(result)
        result = self.pri_root.sh(
            'systemctl enable ntpd').result()
        self.log.info(result)
        result = self.pri_root.sh('systemctl start ntpd').result()
        self.log.info(result)

    def test_ddl_database(self):
        self.log.info('-----1.创建数据库-----')
        sql = f'drop database if exists {self.dbname};' \
            f'create database {self.dbname};'
        result = self.pri_sh.executDbSql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS_MSG, result)
        sql = f'drop database if exists {self.dbname1};' \
            f'create database {self.dbname1};'
        result = self.pri_sh.executDbSql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS_MSG, result)

        self.log.info('-----2.创建表空间------')
        sql = f"create tablespace {self.dbspace} " \
            f"RELATIVE LOCATION '{self.dbspace}';"
        result = self.pri_sh.executDbSql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TBLSPACE_SUCCESS_MSG, result)

        self.log.info("-------3.设置操作系统时间为02/28/2020 23:59:00-----")
        datecmd = 'date -s \'02/28/2020 23:59:00\''
        result = self.pri_root.sh(datecmd)
        self.log.info(datecmd)
        self.log.info(result)

        datecmd = 'hwclock --systohc;service ntpd stop;' \
                  'ntpdate ntp.api.bz;service ntpd start '
        result = self.pri_root.sh(datecmd).result()
        self.log.info(datecmd)
        self.log.info(result)

        result = self.pri_root.sh('date')
        self.log.info(result)
        before_time = self.pri_sh.executDbSql('select now();')
        self.log.info(before_time)
        self.assertIn("2020-02-28", before_time)

        self.log.info('------7.重复3-6 100次----------')
        for i in range(100):
            self.log.info(f'============round:{i}===========')
            self.log.info('--------4.修改数据库-----')
            sql = f"alter database {self.dbname} " \
                f"SET TABLESPACE  {self.dbspace};"
            alter_database1 = ComThread(self.pri_sh.executDbSql,
                                        args=(sql,))
            alter_database1.setDaemon(True)
            alter_database1.start()

            sql = f"alter database {self.dbname1}" \
                f" SET TABLESPACE  {self.dbspace};"
            alter_database2 = ComThread(self.pri_sh.executDbSql,
                                        args=(sql,))
            alter_database2.setDaemon(True)
            alter_database2.start()

            self.log.info('------5.创建数据库的同时执行vacuum--------')
            result = self.pri_sh.executDbSql('VACUUM;')
            self.log.info(result)
            self.assertIn('VACUUM', result)

            self.log.info('================检查创建数据结果====================')
            alter_database1.join(5 * 60)
            result = alter_database1.get_result()
            self.log.info(result)
            self.assertIn('ALTER', result)
            alter_database2.join(5 * 60)
            result = alter_database2.get_result()
            self.log.info(result)
            self.assertIn('ALTER', result)

    def tearDown(self):
        self.log.info('-----环境清理----------')
        self.log.info('========查询主备一致性，辅助定位=========')
        nodes_tuple = (self.pri_user,
                       self.sta1_user, self.sta2_user)
        result = self.pri_sh.check_location_consistency('primary', 3)
        if result:
            table_dict = self.common.format_sql_result(
                self.pri_sh.executDbSql(r'\d'))
            table_name = table_dict.get('Name')
            for name in table_name:
                select_sql = f'select count(*) from {name};'
                flag = self.common.check_data_sample_by_all(
                    select_sql, *nodes_tuple)
                self.log.info(flag)
        self.log.info("------恢复时间-----")
        current = self.sta1_root.sh(
            "date \"+%m/%d/%Y %H:%M:%S\"").result()
        self.log.info(current)

        datecmd = f'date -s "{current}"'
        result = self.pri_root.sh(datecmd).result()
        self.log.info(datecmd)
        self.log.info(result)

        datecmd = 'hwclock --systohc;service ntpd stop;' \
                  'ntpdate ntp.api.bz;service ntpd start '
        result = self.pri_root.sh(datecmd).result()
        self.log.info(datecmd)
        self.log.info(result)
        datecmd = 'hwclock --hctosys;service ntpd stop;' \
                  'ntpdate ntp.api.bz;service ntpd start '
        result = self.pri_root.sh(datecmd).result()
        self.log.info(datecmd)
        self.log.info(result)

        self.log.info('------删除数据库----------')
        result = self.pri_sh.executDbSql(
            f'drop database if exists {self.dbname};')
        self.log.info(result)
        result = self.pri_sh.executDbSql(
            f'drop database if exists {self.dbname1};')
        self.log.info(result)

        self.log.info('------删除表空间----------')
        result = self.pri_sh.executDbSql(
            f'drop tablespace if exists {self.dbspace};')
        self.log.info(result)
        self.log.info('----Opengauss_Reliability_Dbsys_Case236.py 执行结束-----')
